package com.hafu.dao;

import com.hafu.model.SpInfo;
import com.hafu.util.DbUtil;
import com.hafu.util.StringUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class SpInfoDao {
//    添加商品信息
    public Boolean addSpInfo(SpInfo sp){
        String sql = "insert into spinfo (name,price,sell_price) values(?,?,?)";
        Connection conn = DbUtil.getConnection();
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString( 1,sp.getName());
            preparedStatement.setBigDecimal(2,sp.getPrice());
            preparedStatement.setBigDecimal(3,sp.getSellPrice());
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("添加成功");
                return true;
            }else return false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }
//    删除商品信息
    public Boolean deleteSpInfo(int id){
        String sql = "delete from spinfo where id=?";
        Connection conn = DbUtil.getConnection();
        try{
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1,id);
//            执行添加sql方法
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
                return true;
            }else return false;
        }catch(SQLException throwables){
            throwables.printStackTrace();
        }
        return false;
    }
        public Boolean updateSplnfo(SpInfo sp) {
            String sql = "update spinfo set name=?,price=?,sell_price=? where id=?";
            Connection conn = DbUtil.getConnection();
            try {
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1, sp.getName());
                preparedStatement.setBigDecimal(2, sp.getPrice());
                preparedStatement.setBigDecimal(3, sp.getSellPrice());
                preparedStatement.setInt(4, sp.getId());
                int i = preparedStatement.executeUpdate();
                if (i>0){
                    System.out.println("修改成功");
                    return true;
                } else return false;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return false;
        }
            public ArrayList<SpInfo> getSpInfoByName (SpInfo sp){
                String sql = "select * from spinfo";
                if (!StringUtil.isEmpty(sp.getName())) {
                    sql = sql + " where name like '%" + sp.getName() + "%'";
                }
                Connection conn = DbUtil.getConnection();
                ArrayList<SpInfo> spList = new ArrayList<>();
                try {
                    PreparedStatement preparedStatement = conn.prepareStatement(sql);
                    ResultSet resultSet = preparedStatement.executeQuery();
                    while (resultSet.next()) {
                        int id = resultSet.getInt("id");
                        String name = resultSet.getString("name");
                        BigDecimal price = resultSet.getBigDecimal("price");
                        BigDecimal sellPrice = resultSet.getBigDecimal("sell_price");
                        SpInfo spinfo = new SpInfo(id, name, price, sellPrice);
                        spList.add(spinfo);
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                return spList;
            }
        }
